<?php
/**
 * Created by PhpStorm.
 * User: trisatria
 * Date: 1/6/14
 * Time: 3:32 PM
 */

namespace Admin\Model;

use Admin\Model\AdminUser;
use Zend\Db\Sql\Select;
use Zend\Db\Sql\Where;
use Zend\Paginator\Adapter\DbSelect;
use Zend\Paginator\Paginator;
use Zend\Db\ResultSet\ResultSet;
use Zend\Db\TableGateway\TableGateway;
use Zend\Db\Sql\Exception;

/**
 * Class AdminTopicTable
 * @package Admin\Model
 */
class AdminTopicTable
{
    protected $tableGateway;

    public function __construct(TableGateway $tableGateway)
    {
        $this->tableGateway = $tableGateway;
    }

    /**
     * @return Paginator
     */
    public function fetchAll()
    {
        $select = new Select('topic');
        $select->order('up_date DESC');
        // create a new result set based on the Album entity
        $resultSetPrototype = new ResultSet();
        $resultSetPrototype->setArrayObjectPrototype(new AdminTopic());
        // create a new pagination adapter object

        $paginatorAdapter = new DbSelect(
        // our configured select object
            $select,
            // the adapter to run it against
            $this->tableGateway->getAdapter(),
            // the result set to hydrate
            $resultSetPrototype
        );
        $paginator = new Paginator($paginatorAdapter);
        return $paginator;
    }

    /**
     * @param string $tag
     * @return \stdClass
     */
    public function searchByTag($tag = ''){
        $query = "SELECT a.id AS topicid , a.user_id,a.title AS title,a.images,a.price,a.show_price ,a.short_description,a.full_description,a.expire_date,a.status,a.cat_id AS cat_id,a.create_date ,a.slug , a.topic_location AS topic_location, a.viewed,a.tag ,a.topic_type AS topic_type ,b.name,b.email,b.address,b.phone,b.avatar,b.zalo , b.kakaotalk, b.viber FROM topic a  JOIN user b ON b.id = a.user_id WHERE a.tag = " . $tag;
        $driver = $this->tableGateway->getAdapter()->getDriver();
        $connection = $driver->getConnection();
        $result = $connection->execute($query);

        $statement = $result->getResource();
        $resultSet1 = $statement->fetchAll(\PDO::FETCH_NAMED);
        // $resultSet1 = $statement->fetchAll(\Pdo::FETCH_OBJ);
        $object = new \stdClass();
        foreach ($resultSet1[0] as $keys => $value) {
            $object->$keys = $value;
        }
        return $object;
    }

    /**
     * @return Paginator
     */

    public function fetchTopicWhere($where = array())
    {
        //get all message of user

        $select = new Select('topic');
        $select->where($where);
        $select->order('up_date DESC');
        // create a new result set based on the Album entity
        $resultSetPrototype = new ResultSet();
        $resultSetPrototype->setArrayObjectPrototype(new AdminTopic());
        // create a new pagination adapter object
        $paginatorAdapter = new DbSelect(
        // our configured select object
            $select,
            // the adapter to run it against
            $this->tableGateway->getAdapter(),
            // the result set to hydrate
            $resultSetPrototype
        );
        $paginator = new Paginator($paginatorAdapter);
        return $paginator;
    }

    /**
     * @param $topic_id
     * @return \stdClass
     */
    public function getTopicJoin($topic_id)
    {
        $query = "SELECT a.id AS topicid , a.user_id,a.title AS title,a.images,a.price,a.show_price ,a.short_description,a.full_description,a.expire_date,a.status,a.cat_id AS cat_id,a.create_date ,a.slug , a.topic_location AS topic_location, a.viewed ,a.topic_type AS topic_type ,b.name,b.email,b.address,b.phone,b.avatar,b.zalo , b.kakaotalk, b.viber FROM topic a  JOIN user b ON b.id = a.user_id WHERE a.id = " . $topic_id;
        $driver = $this->tableGateway->getAdapter()->getDriver();
        $connection = $driver->getConnection();
        $result = $connection->execute($query);

        $statement = $result->getResource();
        $resultSet1 = $statement->fetchAll(\PDO::FETCH_NAMED);
        // $resultSet1 = $statement->fetchAll(\Pdo::FETCH_OBJ);
        $object = new \stdClass();
        foreach ($resultSet1[0] as $keys => $value) {
            $object->$keys = $value;
        }
        return $object;
    }

    /**
     * @param $slug
     * @return \stdClass
     */

    public function getTopicJoinBySlug($slug)
    {
        $query = "SELECT a.id AS topicid , a.user_id,a.title AS title,a.images,a.price,a.show_price ,a.short_description,a.full_description,a.expire_date,a.status,a.cat_id AS cat_id,a.create_date, a.topic_location AS topic_location, a.viewed, a.topic_type AS topic_type ,a.slug ,b.name,b.email,b.address,b.phone,b.avatar,b.zalo , b.kakaotalk, b.viber FROM topic a  JOIN user b ON b.id = a.user_id WHERE a.slug = '" . $slug . " '";
        $driver = $this->tableGateway->getAdapter()->getDriver();
        $connection = $driver->getConnection();
        $result = $connection->execute($query);

        $statement = $result->getResource();
        $resultSet1 = $statement->fetchAll(\PDO::FETCH_NAMED);
        // $resultSet1 = $statement->fetchAll(\Pdo::FETCH_OBJ);
        if(count($resultSet1) == 0){
            return null;
        }
        $object = new \stdClass();
        foreach ($resultSet1[0] as $keys => $value) {
            $object->$keys = $value;
        }
        return $object;
    }

    public function getTopicByUser($user_id, $paginated = true, $search = null, $where = null)
    {

        if ($paginated) {
            //get all message of user
            $select = new Select('topic');
            $select->where(array('user_id = ' . $user_id));
            if ($search != null) {
                $select->where(array(' title like "%' . $search . '%"'));
            }
            if ($where != null) {
                $select->where($where);
            }
            $select->order('up_date DESC');

            // create a new result set based on the Album entity
            $resultSetPrototype = new ResultSet();
            $resultSetPrototype->setArrayObjectPrototype(new AdminTopic());
            // create a new pagination adapter object
            $paginatorAdapter = new DbSelect(
            // our configured select object
                $select,
                // the adapter to run it against
                $this->tableGateway->getAdapter(),
                // the result set to hydrate
                $resultSetPrototype
            );
            $paginator = new Paginator($paginatorAdapter);
            return $paginator;
        }
        $resultSet = $this->tableGateway->select();
        return $resultSet;


    }

    /**
     * @param AdminTopic $topic
     * @return int|null
     */
    public function saveTopic(AdminTopic $topic)
    {


//        echo '<pre>';
//        print_r($topic);
//        echo '</pre>';
        $data = array(
            'user_id' => $topic->user_id,
            'title' => $topic->title,
            'images' => $topic->images,
            'price' => $topic->price,
            'show_price' => $topic->show_price,
            'short_description' => $topic->short_description,
            'full_description' => $topic->full_description,
            'expire_date' => $topic->expire_date,
            'status' => $topic->status,
            'cat_id' => $topic->cat_id,
            'create_date' => $topic->create_date,
            'topic_type' => $topic->topic_type,
            'topic_location' => $topic->topic_location,
            'is_brandnew' => $topic->is_brandnew,
            'thumb' => $topic->thumb,
            'up_date' => $topic->up_date,
            'slug' => $topic->slug,
            'is_public' => $topic->is_public,
            'viewed' => $topic->viewed,
        );

        $id = (int)$topic->id;
        if ($id == null) {
            $this->tableGateway->insert($data);
            return $this->tableGateway->getLastInsertValue();
        } else {
            if ($this->getTopic($id)) {
                $this->tableGateway->update($data, array('id' => $id));
                return $id;
            } else {
                return null;
            }
        }
    }

    /**
     * @param $id
     * @return array|\ArrayObject|null
     * @throws \Exception
     */

    public function getTopic($id)
    {
        $id = (int)$id;
        $rowset = $this->tableGateway->select(array('id' => $id));
        $row = $rowset->current();

        if (!$row) {
            throw new \Exception("Could not find row $id");
        }
        return $row;
    }

    public function getTopicByCateId($cate_id = null)
    {
          if($cate_id == null)
          return null;
        $query = "SELECT count(id) FROM topic WHERE cat_id = '$cate_id'";
        $driver = $this->tableGateway->getAdapter()->getDriver();
        $connection = $driver->getConnection();
        $result = $connection->execute($query);

        $statement = $result->getResource();
        $resultSet1 = $statement->fetchAll(\PDO::FETCH_NUM);
        return $resultSet1;
    }

    /**
     * @param $slug
     * @return array|\ArrayObject|null
     * @throws \Exception
     */
    public function getTopicByslug($slug)
    {

        $rowset = $this->tableGateway->select(array('slug' => $slug));
        $row = $rowset->current();

        if (!$row) {
            throw new \Exception("Could not find row $slug");
        }
        return $row;
    }

    /**
     * @return \Zend\Db\Adapter\Driver\ResultInterface
     */
    public function del_topic_expire()
    {
        $date = date("Y-m-d");
        $str = "DELETE FROM  `topic` WHERE  `expire_date` < '" . $date . "'";

        $driver = $this->tableGateway->getAdapter()->getDriver();
        $connection = $driver->getConnection();
        $result = $connection->execute($str);
        return $result;
    }

    /**
     * @param $id
     */
    public function deleteTopic($id)
    {
        $this->tableGateway->delete(array('id' => $id));
    }

    /**
     * @param string $val
     * @param string $cat
     * @param string $type
     * @param string $loc
     * @return Paginator
     */
    public function searchFullText($val = '', $cat = '', $type = '', $loc = '')
    {

        $select_array = array();
        $where_array = array();

        $select_array[] = '*';
        if (isset($val) && $val != '') {
            //$select_array[] = new \Zend\Db\Sql\Expression("MATCH(title, full_description, short_description) AGAINST('".$val."') AS score");
            $where_array[] = " `title` LIKE N'%" . $val . "%' ";
        }
        if (isset($cat) && $cat != '' && $cat != 0) {
            $where_array[] = 'cat_id = ' . $cat;
        }

        if (isset($loc) && $loc != '' && $loc != 0) {
            $where_array[] = 'topic_location = ' . $loc;
        }


        if (isset($type) && $type != '') {
            $where_array[] = 'topic_type = ' . $type;
        }

        $where_array[] = 'status = 1';
        $select = new Select();
        $select->columns($select_array);
        $select->from('topic');
        $select->where($where_array);
        $select->order('up_date DESC');
        if ($val && $val != '') {
            //$select->order(' DESC');
        }


        $resultSetPrototype = new ResultSet();
        $resultSetPrototype->setArrayObjectPrototype(new AdminTopic());
        //echo $select->getSqlString();
        // create a new pagination adapter object
        $paginatorAdapter = new DbSelect(
        // our configured select object
            $select,
            // the adapter to run it against
            $this->tableGateway->getAdapter(),
            // the result set to hydrate
            $resultSetPrototype
        );
        $paginator = new Paginator($paginatorAdapter);
        return $paginator;
    }

    /**
     * @param $user_id
     * @param $from_row
     * @param $to_row
     * @return mixed
     */
    public function fetchTopicIndex($user_id, $from_row, $to_row)
    {
        $str = 'SELECT `topic`.`id` AS `topi` , `topic`.`user_id`,`topic`.`title`,`topic`.`images`,`topic`.`price`,`topic`.`show_price` ,`topic`.`short_description`,`topic`.`full_description`,`topic`.`expire_date`,`topic`.`status`,`topic`.`cat_id`,`topic`.`tag` AS `tocatid`,`topic`.`create_date`, `topic`.topic_location AS topic_location, `topic`.topic_type AS topic_type FROM topic WHERE user_id=' . $user_id . ' ORDER BY up_date DESC limit ' . $from_row . ', ' . $to_row;

        $driver = $this->tableGateway->getAdapter()->getDriver();
        $connection = $driver->getConnection();
        $result = $connection->execute($str);

        $statement = $result->getResource();
        $resultSet1 = $statement->fetchAll(\Pdo::FETCH_NAMED);
        // $resultSet1 = $statement->fetchAll(\Pdo::FETCH_OBJ);
        return $resultSet1;

    }


    // fetch topic data 20 item per request
    // fetch for user has already friends

    /**
     * @param null $from_row
     * @param null $to_row
     * @return mixed
     */
    public function fetch_topic_for_new_user($from_row = null, $to_row = null)
    {
        if ($from_row == null && $to_row == null) {
            $from_row = 0;
            $to_row = 1000;
        }


        $driver = $this->tableGateway->getAdapter()->getDriver();
        $connection = $driver->getConnection();
        $result = $connection->execute('CALL fetch_by_new_user(' . $from_row . ',' . $to_row . ')');

        $statement = $result->getResource();
        $resultSet1 = $statement->fetchAll(\Pdo::FETCH_NAMED);
        // $resultSet1 = $statement->fetchAll(\Pdo::FETCH_OBJ);
        return $resultSet1;
    }

    // fetch topic for new user

    /**
     * @param null $userid
     * @param null $limit
     * @return mixed
     */
    public function getTopicOfUser($userid = null, $limit = null)
    {
        $driver = $this->tableGateway->getAdapter()->getDriver();
        $connection = $driver->getConnection();
        $result = $connection->execute('CALL getTopicOfUser(' . $userid . ',' . $limit . ')');

        $statement = $result->getResource();
        $resultSet1 = $statement->fetchAll(\Pdo::FETCH_NAMED);
        // $resultSet1 = $statement->fetchAll(\Pdo::FETCH_OBJ);
        return $resultSet1;
    }

    /**
     * @return mixed
     */

    public function fetch_new_topic()
    {
        $this->optimizeDB('topic');
        $driver = $this->tableGateway->getAdapter()->getDriver();
        $connection = $driver->getConnection();
        $result = $connection->execute('CALL fetch_new_topic()');

        $statement = $result->getResource();
        $resultSet1 = $statement->fetchAll(\Pdo::FETCH_NAMED);
        // $resultSet1 = $statement->fetchAll(\Pdo::FETCH_OBJ);
        return $resultSet1;
    }

    public function optimizeDB($table = null)
    {
        $query = " ANALYZE TABLE `" . $table . '`';
        $driver = $this->tableGateway->getAdapter()->getDriver();
        $connection = $driver->getConnection();
        $result = $connection->execute($query);
        $query = " OPTIMIZE TABLE `" . $table . '`';
        $driver = $this->tableGateway->getAdapter()->getDriver();
        $connection = $driver->getConnection();
        $result = $connection->execute($query);
    }

    /**
     * @return Paginator
     */
    public function fetch()
    {

        $select = new Select('topic');
        $select->order('up_date DESC');
        $select->where(array('status' => 1));
        // create a new result set based on the Album entity
        $resultSetPrototype = new ResultSet();
        $resultSetPrototype->setArrayObjectPrototype(new AdminTopic());

        // create a new pagination adapter object
        $paginatorAdapter = new DbSelect(
        // our configured select object
            $select,
            // the adapter to run it against
            $this->tableGateway->getAdapter(),
            // the result set to hydrate
            $resultSetPrototype
        );
        $paginator = new Paginator($paginatorAdapter);
        return $paginator;

    }

    /**
     * @param $val
     * @return mixed
     */

    public function searchFullTextArray($val)
    {
        $str = "SELECT *, MATCH(title, full_description, short_description) AGAINST('" . $val . "') AS score FROM `topic` WHERE  MATCH(title, full_description, short_description) AGAINST ('" . $val . "') ORDER BY score DESC ";

        $driver = $this->tableGateway->getAdapter()->getDriver();
        $connection = $driver->getConnection();
        $result = $connection->execute($str);
        $statement = $result->getResource();
        $resultSet1 = $statement->fetchAll(\Pdo::FETCH_NAMED);
        // $resultSet1 = $statement->fetchAll(\Pdo::FETCH_OBJ);
        return $resultSet1;
    }

    /**
     * @param null $property
     * @return null|Paginator
     */

    public function RelatedProduct($property = null)
    {
        if ($property != null) {
            $select = new Select('topic');

            $where = new Where();
            foreach ($property as $key => $val) {

                $where->equalTo($key, $val)->OR;

            }

            $select->where($where);

            $select->order('up_date DESC');

            $resultSetPrototype = new ResultSet();
            $resultSetPrototype->setArrayObjectPrototype(new AdminTopic());

            $paginatorAdapter = new DbSelect(
            // our configured select object
                $select,
                // the adapter to run it against
                $this->tableGateway->getAdapter(),
                // the result set to hydrate
                $resultSetPrototype
            );
            $paginator = new Paginator($paginatorAdapter);
            return $paginator;
        }
        return null;

    }

    /**
     * @param null $property
     * @return null|\Zend\Db\Adapter\Driver\ResultInterface
     */
    public function viewed_products($property = null)
    {
        if ($property != null) {
            $where = ' ';
            $i = 0;

            if (is_array($property)) {
                foreach ($property as $val) {

                    $i++;
                    if ($i == 1) {
                        $where .= " `id` = " . (int)$val;
                    }

                    if ($i > 1) {
                        $where .= " OR `id` = " . (int)$val . " ";
                    }
                }
            } else {
                $where = " `id` =  " . $property;
            }

            $str = " SELECT * FROM `topic` WHERE " . $where . " LIMIT 0,4";

            $driver = $this->tableGateway->getAdapter()->getDriver();
            $connection = $driver->getConnection();

            $result = $connection->execute($str);

            return $result;
        }
        return null;
    }

    /**
     * @param $from_row
     * @param $to_row
     * @param array $filter
     * @return mixed
     */
    public function filterBy($from_row, $to_row, $filter = array())
    {
        $where = '';
        if ($filter['user_id'] != '' && isset($filter['user_id'])) {
            $fil['user_id'] = $filter['user_id'];
        }
        if ($filter['title'] != '' && isset($filter['title'])) {
            $fil['title'] = $filter['title'];
        }
        if ($filter['cat'] != '' && isset($filter['cat'])) {
            $fil['cat_id'] = $filter['cat'];
        }
        if (isset($filter['loc']) && $filter['loc'] != '') {
            $fil['topic_location'] = $filter['loc'];
        }
        if (isset($filter['type']) && $filter['type'] != '') {
            $fil['topic_type'] = $filter['type'];
        }
        if (isset($filter['ibn']) && $filter['ibn'] != '') {
            $fil['is_brandnew'] = $filter['ibn'];
        }
        $fil['status'] = 1;

        $i = 0;
        foreach ($fil as $key => $val) {
            $i++;
            if ($i == 1) {
                if ($val != '' && $val != null && $key != 'title') {
                    $where .= " `" . $key . "` = " . $val;
                }
                if ($val != '' && $val != null && $key == 'title') {
                    $where .= ' title LIKE "%' . $val . '%"';
                }
            }
            if ($i > 1) {
                if ($val != '' && $val != null && $key != 'title') {
                    $where .= " AND `" . $key . "` = " . $val . " ";
                }
                if ($val != '' && $val != null && $key == 'title') {
                    $where .= ' AND title  LIKE "%' . $val . '%"';
                }
            }

        }
        $str = 'SELECT `topic`.`id` AS `topi` , `topic`.`user_id`,`topic`.`title` as `title`,`topic`.`images`,`topic`.`price`,`topic`.`show_price` ,`topic`.`short_description`,`topic`.`full_description`,`topic`.`expire_date`,`topic`.`status`,`topic`.`cat_id` AS `tocatid`,`topic`.`create_date`, `topic`.topic_location AS topic_location, `topic`.topic_type AS topic_type FROM topic WHERE ' . $where . ' ORDER BY up_date DESC limit ' . $from_row . ', ' . $to_row;
        $driver = $this->tableGateway->getAdapter()->getDriver();
        $connection = $driver->getConnection();
        $result = $connection->execute($str);

        $statement = $result->getResource();
        $resultSet1 = $statement->fetchAll(\Pdo::FETCH_NAMED);
        // $resultSet1 = $statement->fetchAll(\Pdo::FETCH_OBJ);
        return $resultSet1;

    }

    /**
     * @param array $where
     * @param null $offset
     * @param null $limit
     * @return mixed
     */
    public function getTopicWhere($where = array(), $offset = null, $limit = null)
    {
        $where_sql = '';
        $i = 0;
        foreach ($where as $key => $value) {
            $i++;
            if ($i == 1) {
                $where_sql .= " `" . $key . "` = " . $value;
            }
            if ($i > 1) {

                $where_sql .= " AND `" . $key . "` =  " . $value;

            }
        }
        $str = " SELECT `topic`.`id` AS `topi` , `topic`.`user_id`,`topic`.`title` as `title`,`topic`.`images`,`topic`.`price`,`topic`.`show_price` ,`topic`.`short_description`,`topic`.`full_description`,`topic`.`expire_date`,`topic`.`status`,`topic`.`cat_id` AS `tocatid`,`topic`.`create_date`, `topic`.topic_location AS topic_location, `topic`.topic_type AS topic_type FROM topic WHERE " . $where_sql . " ORDER BY up_date DESC";
        if ($limit != null && $offset != null) {
            $str = $str .= ' limit ' . $offset . ', ' . $limit;
        }
        $driver = $this->tableGateway->getAdapter()->getDriver();
        $connection = $driver->getConnection();
        $result = $connection->execute($str);

        $statement = $result->getResource();
        $resultSet1 = $statement->fetchAll(\Pdo::FETCH_NAMED);
        // $resultSet1 = $statement->fetchAll(\Pdo::FETCH_OBJ);
        return $resultSet1;
    }

    /**
     * @return mixed
     */

    public function typeahead()
    {
        $query = "SELECT title FROM topic WHERE status = 1 ORDER BY create_date DESC, up_date DESC";
        $driver = $this->tableGateway->getAdapter()->getDriver();
        $connection = $driver->getConnection();
        $result = $connection->execute($query);

        $statement = $result->getResource();
        $resultSet1 = $statement->fetchAll(\PDO::FETCH_NAMED);
        // $resultSet1 = $statement->fetchAll(\Pdo::FETCH_OBJ);
        return $resultSet1;
    }

    /**
     * @param null $user_id
     * @return mixed
     */
    public function typeaheadUser($user_id = null)
    {
        $query = "SELECT title FROM topic WHERE user_id = '" . $user_id . "'  ORDER BY create_date DESC, up_date DESC";
        $driver = $this->tableGateway->getAdapter()->getDriver();
        $connection = $driver->getConnection();
        $result = $connection->execute($query);

        $statement = $result->getResource();
        $resultSet1 = $statement->fetchAll(\PDO::FETCH_NAMED);
        return $resultSet1;
    }

    /**
     * @param $view
     * @param $id
     */
    public function viewtopic($view, $id)
    {
        $this->tableGateway->update(array('viewed' => $view), array('id' => $id));
    }


    public function  fetchTopicForSiteMap(){
        $query = "SELECT * FROM topic   ORDER BY create_date DESC";
        $driver = $this->tableGateway->getAdapter()->getDriver();
        $connection = $driver->getConnection();
        $result = $connection->execute($query);

        $statement = $result->getResource();
        $resultSet1 = $statement->fetchAll(\PDO::FETCH_NAMED);
        // $resultSet1 = $statement->fetchAll(\Pdo::FETCH_OBJ);
        return $resultSet1;
    }


}
